﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Diagnostics;

namespace xml_to_db
{
    public class SQL80SchemaAdapter : ScriptBasedAdapter, ISchemaAdapter
    {
      
#region ISchemaAdapter implementation.

        // ISchemaAdapter implementation.
        void ISchemaAdapter.Create(System.Data.DataSet schema, bool force)
        {
            CreateSQL(schema, force);
        }

        void ISchemaAdapter.Create(System.Data.DataSet schema, bool force, string TablePrefix)
        {
            CreateSQL(schema, force, TablePrefix);
        }

        void ISchemaAdapter.Create(System.Data.DataSet schema, bool force, string TablePrefix, string DbOwner)
        {
            CreateSQL(schema, force, TablePrefix, DbOwner);
        }

        void CreateSQL(DataSet schema, bool force, string TablePrefix = "", string DbOwner = "dbo")
        {
            this.TablePrefix = TablePrefix;
            this.DbOwner = DbOwner;

            using (IDbConnection conn = this.GetConnection())
            {
                conn.Open();
                //
                //  Create database
                //
                string script = GetCreateScript(schema.DataSetName, force);
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    Debug.Assert(script.Length > 0);
                    cmd.CommandText = script;
                    cmd.ExecuteNonQuery();
                }
            }

            using (IDbConnection conn = this.GetConnection(schema.DataSetName))
            {
                conn.Open();

                //
                // Set up the schema
                //
                string script = GetSchemaScript(schema);
                if (script.Length > 0)
                {
                    using (IDbCommand cmd = conn.CreateCommand())
                    {
                        Debug.Assert(script.Length > 0);
                        cmd.CommandText = script;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
           
            using (IDbConnection conn = this.GetConnection(schema.DataSetName))
            {
                conn.Open();

                //
                // Set up the data if any
                //
                string script = GetDataScript(schema);
                if (script.Length > 0)
                {
                    using (IDbCommand cmd = conn.CreateCommand())
                    {
                        Debug.Assert(script.Length > 0);
                        cmd.CommandText = script;
                        cmd.ExecuteNonQuery();
                    }
                }
            }


        }

#endregion.

#region ScriptBasedAdapter implementation.        

        // ScriptBasedAdapter implementation.
        public string TablePrefix {get; set;}
        public string DbOwner {get; set;}
        public readonly string Host;
        public readonly Hashtable TypeMap;
        public readonly Hashtable TypeDataMap;

        // construtor
        public SQL80SchemaAdapter(string host)
        {            
            this.TypeMap = new Hashtable();
            this.TypeMap[typeof(UInt64)] = "bigint {1}NULL";
            this.TypeMap[typeof(Int64)] = "bigint {1}NULL";
            this.TypeMap[typeof(Boolean)] = "bit {1}NULL";
            this.TypeMap[typeof(Char)] = "char {1}NULL";
            this.TypeMap[typeof(DateTime)] = "datetime {1}NULL";
            this.TypeMap[typeof(Double)] = "float {1}NULL";
            this.TypeMap[typeof(UInt32)] = "int {1}NULL";
            this.TypeMap[typeof(Int32)] = "int {1}NULL";
            this.TypeMap[typeof(Guid)] = "uniqueidentifier {1}NULL";
            this.TypeMap[typeof(UInt16)] = "smallint {1}NULL";
            this.TypeMap[typeof(Int16)] = "smallint {1}NULL";
            this.TypeMap[typeof(Decimal)] = "real {1}NULL";
            this.TypeMap[typeof(Byte)] = "tinyint {1}NULL";
            this.TypeMap[typeof(String)] = "varchar({0}) {1}NULL";
            this.TypeMap[typeof(TimeSpan)] = "int {1}NULL";
            this.TypeMap[typeof(Byte[])] = "varbinary {1}NULL";

            this.TypeDataMap = new Hashtable();
            this.TypeDataMap[typeof(UInt64)] = "{0:d}";
            this.TypeDataMap[typeof(Int64)] = "{0:d}";
            this.TypeDataMap[typeof(Boolean)] = "{0}";
            this.TypeDataMap[typeof(Char)] = "'{0}'";
            this.TypeDataMap[typeof(DateTime)] = "{0:yyyy-MM-dd}";
            this.TypeDataMap[typeof(Double)] = "{0:c}";
            this.TypeDataMap[typeof(UInt32)] = "{0:d}";
            this.TypeDataMap[typeof(Int32)] = "{0:d}";
            this.TypeDataMap[typeof(Guid)] = "{0}";
            this.TypeDataMap[typeof(UInt16)] = "{0:d}";
            this.TypeDataMap[typeof(Int16)] = "{0:d}";
            this.TypeDataMap[typeof(Decimal)] = "{0:d}";
            this.TypeDataMap[typeof(Byte)] = "{0:d}";
            this.TypeDataMap[typeof(String)] = "'{0}'";
            this.TypeDataMap[typeof(TimeSpan)] = "{0:yyyy-MM-dd HH:mm:ss}";
            this.TypeDataMap[typeof(Byte[])] = "{0:d}";
            
            this.Host = host;
        }

        protected override IDbConnection GetConnection()
        {
            return this.GetConnection(null); 
        }

        protected override IDbConnection GetConnection(string catalog)
        {
            StringBuilder connectionString = new StringBuilder();
            connectionString.AppendFormat(@"Data Source={0};Integrated Security=SSPI;Pooling='false';", this.Host);

            if ((catalog != null) && (catalog.Length != 0))
            {
                connectionString.AppendFormat(@"Initial Catalog='{0}'", catalog);
            }
            return new SqlConnection(connectionString.ToString());
        }


        protected override string  GetCreateScript(string databaseName, bool overwrite = false)
        {
 	        if ((databaseName == null) || (databaseName.Trim().Length == 0))
                throw new ArgumentException(String.Format("The database name passed is{0}",((databaseName == null) ? "null" : "empty")),"databaseName");

            StringBuilder command = new StringBuilder();

            if (overwrite)            
                 command.AppendFormat("IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'{0}') DROP DATABASE {1};\n CREATE DATABASE {1};\n", databaseName, MakeSafe(databaseName));            
            else            
                command.AppendFormat("IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'{0}') BEGIN USE {1}; END\n ELSE\n BEGIN\n CREATE DATABASE {1};\n END;\n", databaseName, MakeSafe(databaseName));            

            return command.ToString();
        }


        protected override string  GetSchemaScript(DataSet schema)
        {
            if (schema == null)
                throw new ArgumentNullException("null isn't valid parameter value", "schema");

            StringBuilder command = new StringBuilder();
            
            foreach (DataTable table in schema.Tables)
                try
                {
                    command.Append(MakeTable(table));
                }
                catch (ArgumentException exception)
                {
                    throw new ArgumentException("Table does not contain any columns", table.TableName, exception);
                }

            foreach (DataTable table in schema.Tables)                
                if (table.PrimaryKey.Length > 0)
                {
                    string tableName = MakeSafe(this.TablePrefix + table.TableName);
                    string primaryKeyName = MakeSafe("PK_" + this.TablePrefix + table.TableName);
                    string primaryKeyList = MakeColumnList(table.PrimaryKey);
                    command.AppendFormat("ALTER TABLE {0} WITH NOCHECK ADD CONSTRAINT {1} PRIMARY KEY CLUSTERED ({2});\n", tableName, primaryKeyName, primaryKeyList);
                }

            foreach (DataRelation relation in schema.Relations)
                try
                {
                    command.Append(MakeRelation(relation));                    
                }
                catch (ArgumentException exception)
                {
                    throw new ArgumentException("Relationship has an empty column list", relation.RelationName, exception);
                }

            return command.ToString();
            
        }

        protected override string GetDataScript(DataSet schema)
        {
            if (schema == null)
                throw new ArgumentNullException("null isn't valid parameter value", "schema");

            if ((schema.GetXml() == null )|| (schema.GetXml().Length == 0))
                return "";

            
            StringBuilder command = new StringBuilder();
            foreach (DataTable table in schema.Tables)
            {
                if (table.Rows.Count != 0)
                    command.Append(MakeData(table));
            }

            return command.ToString();
        }


        protected override string MakeData(DataTable table)
        {
            if (table == null)
                throw new ArgumentNullException("Invalid arguement value (null)", "table");


            Boolean isFirstColumnItem = true;
            StringBuilder command = new StringBuilder();
            int intRowIndex = 0;
            command.AppendFormat("INSERT INTO {0} \n(\n",this.TablePrefix + MakeSafe(table.TableName));

            foreach (DataColumn col in table.Columns)
            {
                if (!isFirstColumnItem)
                    command.Append(",");

                command.AppendFormat("{0}\n", MakeSafe(col.ColumnName));
                isFirstColumnItem = false;
            }

            command.Append(")\n");
            
            foreach (DataRow item in table.Rows)
            {
                command.Append("SELECT \n");
                isFirstColumnItem = true;   //reset for maniuplate rows data.
                foreach (DataColumn col in table.Columns)
                {              
                    if (!isFirstColumnItem)
                        command.Append(",");

                    command.AppendFormat("{0}\n", GetTypeForData(col, item[col.ColumnName]));
                    isFirstColumnItem = false;

                    if ((col == table.Columns[table.Columns.Count - 1])
                       && (intRowIndex != table.Rows.Count - 1))
                        command.Append("UNION ALL \n");                   
                }
                intRowIndex++;              
            }


            command.Append(" \nGO\n");

            return command.ToString();
        }


        protected override string MakeRelation(DataRelation relation)
        {
            if (relation == null)
                throw new ArgumentNullException("Invalid arguement value (null)", "relation");

            StringBuilder command = new StringBuilder();
            string childTable = MakeSafe(this.TablePrefix + relation.ChildTable.TableName);
            string parentTable = MakeSafe(this.TablePrefix + relation.ParentTable.TableName);
            string relationName = MakeSafe(relation.RelationName);
            string childColumns = MakeColumnList(relation.ChildColumns);
            string parentColumns = MakeColumnList(relation.ParentColumns);
            command.AppendFormat("ALTER TABLE {0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3} ({4});\n", childTable, relationName, childColumns, parentTable, parentColumns);
            return command.ToString();
        }


        protected override string MakeTable(DataTable table)
        {
            StringBuilder command = new StringBuilder();

            string tableName = MakeSafe(this.TablePrefix + table.TableName);
            string tableColumns = MakeColumnMetaTypeList(table.Columns);
            command.AppendFormat("if exists (select * from dbo.sysobjects where id = object_id(N'{0}') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE {0};", tableName);
            command.AppendFormat("CREATE TABLE {0} ({1});\n", tableName, tableColumns);
            return command.ToString();
        }

        protected override string MakeColumnList(DataColumn[] columns)
        {
            if ((columns == null) || (columns.Length < 1))
                throw new ArgumentException("Invalid column list!", "columns");

            StringBuilder list = new StringBuilder();
            bool isFirstColumn = true;
            foreach (DataColumn c in columns)
            {
                if (!isFirstColumn)
                    list.Append(",");

                list.Append(MakeSafe(c.ColumnName));
                isFirstColumn = false;
            }
            return list.ToString();
        }


        protected override string MakeColumnMetaTypeList(DataColumnCollection columns)
        {
            if ((columns == null) || (columns.Count < 1))
                throw new ArgumentException("Invalid column list!", "columns");

            StringBuilder list = new StringBuilder();
            bool isFirstColumn = true;
            foreach (DataColumn c in columns)
            {
                if (!isFirstColumn)
                    list.Append(",");

                string columnName = MakeSafe(c.ColumnName);
                string columnType = GetTypeFor(c);                
                list.AppendFormat("{0} {1}", columnName, columnType);

                isFirstColumn = false;
            }

            return list.ToString();
        }

        protected override string GetTypeFor(DataColumn column)
        {

            string template = (string)TypeMap[column.DataType];

            if ((column.DataType == typeof(String))
                && (column.MaxLength < 0))
            {
                template = "text";
            }

            if (template == null)
                throw new NotSupportedException(
                    String.Format("No type mapping is provided for {0}",
                                    column.DataType.Name));

            return String.Format(template, column.MaxLength, (column.AllowDBNull ? String.Empty : "NOT "));       
        }

        protected override string GetTypeForData(DataColumn column, object obj)
        {
            string template = (string)TypeDataMap[column.DataType];

            if (template == null)
                throw new NotSupportedException(
                    String.Format("No type mapping is provided for {0}",
                                    column.DataType.Name));

            if (obj.ToString().Length == 0 && column.AllowDBNull)
                return String.Format("{0}","NULL");
            else
            {
                if (column.DataType == typeof(Boolean))
                    obj = (Boolean)obj ? "1" : "0" ;

                if (column.DataType == typeof(String))
                    obj = (String)obj.ToString().Replace("'", "''");
            }
            return String.Format(template, obj);
        }


        //
        //  SQL Servername cannot be more than 128 characters long.
        //  trims given name and wraps the name in squara bracket [];
        internal string MakeSafe(string inputvalue)
        {
            String text = inputvalue.Trim();
            return String.Format(
                "[{0}]",
                text.Substring(0, Math.Min(128, text.Length)));
        }

#endregion

    }
}
